/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.database;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.Map;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.driver.OracleBlobInputStream;
import org.users.Agent;
import org.users.Supervisor;

/**
 *
 * @author yakup
 */
public class Database {

    private Connection connection = null;
    private String userName;
    private String password;
    private String portNumber;
    

    public Database() {
        userName = "user";
        password = "1234";
        portNumber = "1521";


    }

    public void connect() throws ClassNotFoundException, SQLException {

        Class.forName("oracle.jdbc.OracleDriver");
        System.out.println("Class Found");
        connection = DriverManager.getConnection("jdbc:oracle:thin:" + userName + "/" + password + "@localhost:" + portNumber + ":XE");
        System.out.println("connected !");

    }

    //ADD_CUSTOMER('12345678902','Emre','GÖZEN','06088972312','emregozen@gmail.com',sysdate-15,1,1,'Alaattin Tepesi');
    public void addCustomer(String tcId, String name, String surname, String gsm, String mektup, Integer p_id, Integer d_id, java.util.Date date, String address) throws SQLException {
        String addCustomerStoredProcedure = "{ call ADD_CUSTOMER(?, ?, ?,?, ?, ? ,?, ?, ?) }";
        CallableStatement cs = connection.prepareCall(addCustomerStoredProcedure);
        java.sql.Date sqlDate = new java.sql.Date(date.getYear(), date.getMonth(), date.getDate());
        // sqlDate = (java.sql.Date)date;
        cs.setString(1, tcId);
        cs.setString(2, name);
        cs.setString(3, surname);
        cs.setString(4, gsm);
        cs.setString(5, mektup);
        cs.setDate(6, sqlDate);
        cs.setInt(7, p_id);
        cs.setInt(8, d_id);
        cs.setString(9, address);

        cs.execute();




    }
    public Map<String,String> getCitiesMap() throws SQLException{
          Map<String,String> cities = new HashMap<String, String>();
          String query = "Select * FROM PROVINCE";
         Statement stmt = connection.createStatement();
         ResultSet rs = stmt.executeQuery(query);
         while(rs.next()){
             cities.put( rs.getString("NAME"),Integer.toString(rs.getInt("P_ID")));
             
         }

          
          
          return cities;
          
    }
     public Map<String,Map<String,String>> getSuburbsMap() throws SQLException{
          Map<String,String> suburbs = null;
           Map<String,Map<String,String>> suburbsData = new HashMap<String, Map<String,String>>();
          String query = "select province.*,district.name as d_name,district.d_id from province,district where province.p_id = district.p_id order by province.p_id";
         Statement stmt = connection.createStatement();
         ResultSet rs = stmt.executeQuery(query);
         int oldId =0;
         while(rs.next()){
             if(oldId!=rs.getInt("P_ID")){
                 if(suburbs != null)
                     suburbsData.put(Integer.toString(oldId), suburbs);
                 suburbs = new HashMap<String, String>();
                 
                     
                 suburbs.put( rs.getString("D_NAME"),Integer.toString(rs.getInt("D_ID")));    
             }
             else{
             suburbs.put( rs.getString("D_NAME"),Integer.toString(rs.getInt("D_ID")));
             }
             oldId=rs.getInt("P_ID");
         }
         if(suburbs != null)
            suburbsData.put(Integer.toString(oldId), suburbs);
          
          
          return suburbsData;
          
    }
     
//     private Map<String,Map<String,String>> suburbsData = new HashMap<String, Map<String,String>>();
//	
//	private Map<String,String> suburbs = new HashMap<String, String>();
//
//	public PprBean() {
//		cities.put("Istanbul", "1");
//		cities.put("Ankara", "2");
//		cities.put("Izmir", "3");
//		
//		Map<String,String> suburbsIstanbul = new HashMap<String, String>();
//		suburbsIstanbul.put("Kadikoy", "1");
//		suburbsIstanbul.put("Levent", "2");
//		suburbsIstanbul.put("Cengelkoy", "3");
//		
//		Map<String,String> suburbsAnkara = new HashMap<String, String>();
//		suburbsAnkara.put("Kecioren", "1");
//		suburbsAnkara.put("Cankaya", "2");
//		suburbsAnkara.put("Yenimahalle", "3");
//		
//		Map<String,String> suburbsIzmir = new HashMap<String, String>();
//		suburbsIzmir.put("Cesme", "1");
//		suburbsIzmir.put("Gumuldur", "2");
//		suburbsIzmir.put("Foca", "3");
//		
//		suburbsData.put("1", suburbsIstanbul);
//		suburbsData.put("2", suburbsAnkara);
//		suburbsData.put("3", suburbsIzmir);
//	}
//    
//  

    public String loginSupervisor(Supervisor supervisor) throws SQLException {
       
//        CallableStatement callableStatement = connection.prepareCall("{? = LOGIN_SUPERVISOR (v_sup_tc_id => ? ,v_user_name => ? , v_password => ? )}");
//        callableStatement.registerOutParameter(1, OracleTypes.BOOLEAN);
//        callableStatement.registerOutParameter(2, OracleTypes.CHAR);
//        callableStatement.setString(2, supervisor.getTC_ID());
//        callableStatement.setString(3, supervisor.getUserName());
//        callableStatement.setString(4, supervisor.getPassword());
//        System.out.println("buraya kadar");
        String query ="SELECT SUP_TC_ID FROM SUPERVISOR WHERE USER_NAME = ? AND PASSWORD = ?";
        PreparedStatement stmt = connection.prepareStatement(query);
        stmt.setString(1, supervisor.getUserName());
        stmt.setString(2, supervisor.getPassword());
        ResultSet rs = stmt.executeQuery();
        String tcId;
        if(rs.next()){
            tcId = rs.getString(1);
            return tcId;
        }else
            return "none";
      
       

       
    }
    
    public Supervisor getSupervisor(String supervisorId) throws SQLException{
        String query = "select p.*,s.*, d.name as district_name,pro.name as province_name from person p ,  SUPERVISOR s,district d,province pro   where s.sup_tc_id = ? and p.tc_id = s.sup_tc_id and p.d_id = d.d_id and p.p_id = pro.p_id ";
        PreparedStatement statement = connection.prepareStatement(query);
        statement.setString(1, supervisorId);
        ResultSet rs = statement.executeQuery();
        Supervisor supervisor = null ;
        while(rs.next()){
        // String userName, String password, String TC_ID, String name, String surname, String gsmNumber, String eMail, Date birthDate, String addressDistrict, String addressResume)
            java.util.Date birthDate = rs.getDate("birth_date");
            
            supervisor = new Supervisor(rs.getString("user_name"),rs.getString("password"),rs.getString("tc_id"),rs.getString("name"),rs.getString("surname"),rs.getString("gsm_number"),rs.getString("e_mail"),birthDate,rs.getString("district_name"),rs.getString("address_resume"),rs.getString("province_name"));
        }
        
        return supervisor;
    }
    
    public String addAgent(Agent agent) throws SQLException{
        //cust_id CHAR,name varchar2,surname varchar2,gsm_number char,e_mail varchar2, birth_date date,dist_id number,pro_id number,address_resume varchar2,v_user_name VARCHAR2,pass out varchar2 
        String addAgentStoredProcedure = "{  call ADD_AGENT(?, ?, ?,?, ?, ? ,?, ?, ?,?,?) }";
        CallableStatement cs = connection.prepareCall(addAgentStoredProcedure);
        java.sql.Date sqlBirthDate = new java.sql.Date(agent.getBirthDate().getYear(), agent.getBirthDate().getMonth(), agent.getBirthDate().getDay());
        cs.registerOutParameter(11, OracleTypes.VARCHAR);
        cs.setString(1,agent.getTC_ID());
        cs.setString(2, agent.getName());
        cs.setString(3, agent.getSurname());
        cs.setString(4, agent.getGsmNumber());
        cs.setString(5, agent.geteMail());
        cs.setDate(6, sqlBirthDate);
        cs.setInt(7, Integer.parseInt(agent.getAddressDistrict()));
        cs.setInt(8, Integer.parseInt(agent.getAddressProvince()));
        cs.setString(9, agent.getAddressResume());
        cs.setString(10, agent.getSurname()+agent.getSurname());
        cs.execute();
        String rs = (String) cs.getObject(11);
        
            return rs;
        
        
    
    
       
    }
    
    public LinkedList<Agent> getAgentList() throws SQLException{
    LinkedList<Agent> agentList = new LinkedList<Agent>();
    String query = "SELECT person.*,agent.*,province.name as p_name, district.name as d_name FROM PERSON,AGENT,DISTRICT,PROVINCE WHERE AGENT.AGENT_TC_ID = PERSON.TC_ID AND PERSON.D_ID = DISTRICT.D_ID and PERSON.P_ID = PROVINCE.P_ID";
    Statement statement = connection.prepareStatement(query);
    ResultSet rs = statement.executeQuery(query);
    while(rs.next()){
        java.util.Date birthDate = (java.util.Date) rs.getDate("BIRTH_DATE");
        agentList.add(new Agent(rs.getString("USER_NAME"),rs.getString("PASSWORD"),rs.getString("TC_ID"),rs.getString("NAME"),rs.getString("SURNAME"),rs.getString("GSM_NUMBER"),rs.getString("E_MAIL"),birthDate,rs.getString("D_NAME"),rs.getString("ADDRESS_RESUME"),rs.getString("P_NAME")));
    
    }
    
     
    
    
    return agentList;
    }
    
     public void deleteAgent(Agent agent) throws SQLException{
        String deleteAgentStoredProcedure = "{ call DELETE_AGENT(?) }";
        CallableStatement cs = connection.prepareCall(deleteAgentStoredProcedure);
        cs.setString(1, agent.getTC_ID());
        cs.execute();
    }
      
    

    public void disconnect() throws SQLException {
        connection.close();
    }
}
